﻿using FAMailUtils.Data.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FAMailUtils.Data.DAO
{
    public class DepartmentDAO
    {
        DbHelper db = new DbHelper();
        public DepartmentDTO GetByDepartmentID(int DepartmentID, int UserID)
        {
            string sql = "select * from tblMB_Departments where DepartmentID = " + DepartmentID + " and UserID = " + UserID;
            DepartmentDTO result = new DepartmentDTO();
            result = (DepartmentDTO)db.GetObject(result, sql);
            return result;
        }

        public DataTable GetAccountByDepartmentID(int DepartmentID, int UserID)
        {
            string sql = @"select a.* from tblMB_Departments d, tblMB_Accounts a
                           where d.IsDelete = 0 and d.DepartmentID = a.DepartmentID and d.UserID = @UserID and d.DepartmentID = @DepartmentID";
            DataTable T = db.GetTable(sql, new object[] { "DepartmentID", DepartmentID, "UserID", UserID });
            return T;
        }

        public DataTable GetAll(int UserID)
        {
            string sql = @"select d.*, (select AccountName from tblMB_Accounts a1 where d.DepartmentID = a1.DepartmentID and a1.IsMainDepartment = 1) MainDepartmentName,
                            (select count(*) from tblMB_Accounts a where d.DepartmentID = a.DepartmentID and a.IsDelete = 0) NumberOfAccounts 
                            from tblMB_Departments d where d.IsDelete = 0 and d.UserID = @UserID";
            DataTable T = db.GetTable(sql, new object[] { "UserID", UserID });
            return T;
        }

        public int RemoveAccountFromDepartment(int AccountID)
        {
            string sql = "update tblMB_Accounts set DepartmentID = NULL where AccountID = @AccountID";
            object obj = db.ExecuteScalar(sql, new object[] { "AccountID", AccountID });
            int result = 0;
            int.TryParse(obj + "", out result);
            return result;
        }

        public bool Edit(DepartmentDTO dto)
        {
            string sql = "update tblMB_Departments set DepartmentName = @DepartmentName, Description = @Description from tblMB_Departments where DepartmentID = @DepartmentID and UserID = @UserID";
            object obj = db.ExecuteScalar(sql, new object[] { "DepartmentID", dto.DepartmentID, "DepartmentName", dto.DepartmentName, "Description", dto.Description, "UserID", dto.UserID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public int Add(DepartmentDTO dto)
        {
            string sql = "insert into tblMB_Departments (DepartmentName, Description, UserID, CreateDate, IsDelete) values (@GroupName, @Description, @UserID, getdate(), @IsDelete)";
            object obj = db.ExecuteScalar(sql, new object[] { "GroupName", dto.DepartmentName, "Description", dto.Description, "UserID", dto.UserID, "IsDelete", false });
            int result = 0;
            int.TryParse(obj + "", out result);
            return result;
        }

        public bool Delete(int DepartmentID, int UserID)
        {
            string sql = "update tblMB_Departments set isDelete = 1, deletedate = getdate() from tblMB_Departments where DepartmentID=@DepartmentID and userid = @userid";
            object obj = db.ExecuteScalar(sql, new object[] { "DepartmentID", DepartmentID, "UserID", UserID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        // Kiểm tra trùng
        public string CheckName(string name, int id, int userid)
        {
            string sql = "select * from tblMB_Departments where userid="+userid+" and IsDelete = 0 and DepartmentName = '" + name + "' and DepartmentID != " + id;
            DepartmentDTO result = new DepartmentDTO();
            result = (DepartmentDTO)db.GetObject(result, sql);
            if (String.IsNullOrWhiteSpace(result.DepartmentName))
            {
                return "";
            }
            else
            {
                return "Tên phòng ban đã tồn tại.";
            }
        }
    }
}
